package com.dao.imp;

import com.dao.JobDao;
import com.entity.FavouriteJob;
import com.entity.Job;
import com.entity.Resume;
import com.entity.User;
import com.mysql.cj.util.StringUtils;
import com.utils.DButil;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class JobDaoimpl implements JobDao {
    @Override
    public List<Object> selectJobList(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String pageStr = req.getParameter("page");
        System.out.println("pageStr = " + pageStr);
        String limitStr = req.getParameter("limit");
        System.out.println("limitStr = " + limitStr);
        String keyName = req.getParameter("keyCompanyName");
        System.out.println("keyName = " + keyName);
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyId = req.getParameter("keyId");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyJobType = req.getParameter("keyJobType");
        String keyState = req.getParameter("keyState");
        int page1 = Integer.parseInt(pageStr);
        int limit1 = Integer.parseInt(limitStr);
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyName) &&
                StringUtils.isNullOrEmpty(keyId) && StringUtils.isNullOrEmpty(keyState)
                && StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(keyJobType)) {
            sql = "SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and (jobstate='未审核' or jobstate='审核通过' or jobstate='审核不通过')  limit ?,?";

            ps = conn.prepareStatement(sql);
            ps.setInt(1, (page1 - 1) * limit1);
            ps.setInt(2, limit1);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyName) ||
                !StringUtils.isNullOrEmpty(keyId) ||
                !StringUtils.isNullOrEmpty(keyState) ||
                !StringUtils.isNullOrEmpty(keyCreateTime) ||
                !StringUtils.isNullOrEmpty(keyEndTime) ||
                !StringUtils.isNullOrEmpty(keyJobType)
        ) {
            sql = "SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and (jobstate='未审核' or jobstate='审核通过' or jobstate='审核不通过')   and r_company.`name` like ?  and r_company.companyid  like ? and jobstate like ?  and createtime >=?  and createtime  <= ?  and jobtype like ? limit ?,? ";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setInt(7, (page1 - 1) * limit1);
            ps.setInt(8, limit1);
            ps.setString(3, "%" + keyState + "%");
            ps.setString(2, "%" + keyId + "%");
            ps.setString(4, keyCreateTime);
            ps.setString(5, keyEndTime);
            ps.setString(6, "%" + keyJobType + "%");
            ps.setString(1, "%" + keyName + "%");
        }
        ResultSet rs = ps.executeQuery();
        List<Object> users = new ArrayList<Object>();
        while (rs.next()) {

            Job job = new Job();
            job.setCompanyId(rs.getString("companyid"));
            job.setCompanyName(rs.getString("name"));
            job.setCompanyAddress(rs.getString("jobaddress"));
            job.setJobNum(rs.getString("jobnum"));
            job.setJobName(rs.getString("jobname"));
            job.setSalary(rs.getString("salary"));
            job.setJobType(rs.getString("jobtype"));
            job.setAddTime(rs.getString("createtime"));
            job.setState(rs.getString("jobstate"));
            job.setAudit_opinion(rs.getString("audit_opinion"));
            job.setJobDescription(rs.getString("jobdescription"));
            job.setJobId(rs.getInt("jobid"));

            users.add(job);
        }
        return users;
    }

    @Override
    public List<Object> selectJobListById(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String pageStr = req.getParameter("page");
        System.out.println("pageStr = " + pageStr);
        String limitStr = req.getParameter("limit");
        System.out.println("limitStr = " + limitStr);
        String keyJobName = req.getParameter("keyJobName");
        System.out.println("keyName = " + keyJobName);
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyJobType = req.getParameter("keyJobType");
        String keyState = req.getParameter("keyState");
        String companyId = req.getParameter("companyId");
        System.out.println("keyJobName = " + keyJobName);
        int page1 = Integer.parseInt(pageStr);
        int limit1 = Integer.parseInt(limitStr);
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyJobName) && StringUtils.isNullOrEmpty(keyState)
                && StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(keyJobType)) {
            sql = "SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户'  and (jobstate='未审核' or jobstate='审核通过' or jobstate='审核不通过')   and r_job.companyid=? limit ?,?";

            ps = conn.prepareStatement(sql);
            ps.setInt(2, (page1 - 1) * limit1);
            ps.setInt(3, limit1);
            ps.setString(1, companyId);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyJobName) ||
                !StringUtils.isNullOrEmpty(keyState) ||
                !StringUtils.isNullOrEmpty(keyCreateTime) ||
                !StringUtils.isNullOrEmpty(keyEndTime) ||
                !StringUtils.isNullOrEmpty(keyJobType)
        ) {
            sql = "SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户'   and (jobstate='未审核' or jobstate='审核通过' or jobstate='审核不通过')    and jobname like ?  and jobstate like ?  and createtime >=?  and createtime  <= ?  and jobtype like ? and r_job.companyid=? limit ?,?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setInt(7, (page1 - 1) * limit1);
            ps.setInt(8, limit1);
            ps.setString(6, companyId);
            ps.setString(2, "%" + keyState + "%");
            ps.setString(3, keyCreateTime);
            ps.setString(4, keyEndTime);
            ps.setString(5, "%" + keyJobType + "%");
            ps.setString(1, "%" + keyJobName + "%");
        }
        ResultSet rs = ps.executeQuery();
        List<Object> users = new ArrayList<Object>();
        while (rs.next()) {

            Job job = new Job();
            job.setCompanyId(rs.getString("companyid"));
            job.setCompanyName(rs.getString("name"));
            job.setCompanyAddress(rs.getString("jobaddress"));
            job.setJobNum(rs.getString("jobnum"));
            job.setJobName(rs.getString("jobname"));
            job.setSalary(rs.getString("salary"));
            job.setJobType(rs.getString("jobtype"));
            job.setAddTime(rs.getString("createtime"));
            job.setState(rs.getString("jobstate"));
            job.setAudit_opinion(rs.getString("audit_opinion"));
            job.setJobDescription(rs.getString("jobdescription"));
            job.setJobId(rs.getInt("jobid"));

            users.add(job);
        }
        return users;
    }

    @Override
    public List<Object> collectJobListById(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String jobId = req.getParameter("jobId");
        System.out.println("jobId = " + jobId);
        String sql = "";
        PreparedStatement ps = null;
        sql = "SELECT * from r_job,r_company where jobid=? and r_job.companyid=r_company.companyid";

        ps = conn.prepareStatement(sql);
        ps.setString(1, jobId);
        System.out.println("sql = " + sql);

        ResultSet rs = ps.executeQuery();
        List<Object> users = new ArrayList<Object>();
        while (rs.next()) {

            Job job = new Job();
            job.setCompanyId(rs.getString("companyid"));
            job.setCompanyName(rs.getString("name"));
            job.setCompanyAddress(rs.getString("jobaddress"));
            job.setJobNum(rs.getString("jobnum"));
            job.setJobName(rs.getString("jobname"));
            job.setSalary(rs.getString("salary"));
            job.setJobType(rs.getString("jobtype"));
            job.setAddTime(rs.getString("createtime"));
            job.setState(rs.getString("jobstate"));
            job.setAudit_opinion(rs.getString("audit_opinion"));
            job.setJobDescription(rs.getString("jobdescription"));
            job.setJobId(rs.getInt("jobid"));

            users.add(job);
        }
        return users;
    }

    @Override
    public List<Object> selectPublishJobListById(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String pageStr = req.getParameter("page");
        System.out.println("pageStr = " + pageStr);
        String limitStr = req.getParameter("limit");
        System.out.println("limitStr = " + limitStr);
        String keyJobName = req.getParameter("keyJobName");
        System.out.println("keyName = " + keyJobName);
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyJobType = req.getParameter("keyJobType");
        String keyState = req.getParameter("keyState");
        String companyId = req.getParameter("companyId");
        System.out.println("keyJobName = " + keyJobName);
        int page1 = Integer.parseInt(pageStr);
        int limit1 = Integer.parseInt(limitStr);
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyJobName) && StringUtils.isNullOrEmpty(keyState)
                && StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(keyJobType)) {
            sql = "SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and r_job.companyid=? and (jobstate='审核通过' OR jobstate='已发布' OR jobstate='已撤销发布' ) limit ?,?";

            ps = conn.prepareStatement(sql);
            ps.setInt(2, (page1 - 1) * limit1);
            ps.setInt(3, limit1);
            ps.setString(1, companyId);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyJobName) ||
                !StringUtils.isNullOrEmpty(keyState) ||
                !StringUtils.isNullOrEmpty(keyCreateTime) ||
                !StringUtils.isNullOrEmpty(keyEndTime) ||
                !StringUtils.isNullOrEmpty(keyJobType)
        ) {
            sql = "SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户'   and jobname like ?  and jobstate like ?  and createtime >=?  and createtime  <= ?  and jobtype like ? and r_job.companyid=? and (jobstate='审核通过' OR jobstate='已发布' OR jobstate='已撤销发布' ) limit ?,?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setInt(7, (page1 - 1) * limit1);
            ps.setInt(8, limit1);
            ps.setString(6, companyId);
            ps.setString(2, "%" + keyState + "%");
            ps.setString(3, keyCreateTime);
            ps.setString(4, keyEndTime);
            ps.setString(5, "%" + keyJobType + "%");
            ps.setString(1, "%" + keyJobName + "%");
        }
        ResultSet rs = ps.executeQuery();
        List<Object> users = new ArrayList<Object>();
        while (rs.next()) {

            Job job = new Job();
            job.setCompanyId(rs.getString("companyid"));
            job.setCompanyName(rs.getString("name"));
            job.setCompanyAddress(rs.getString("jobaddress"));
            job.setJobNum(rs.getString("jobnum"));
            job.setJobName(rs.getString("jobname"));
            job.setSalary(rs.getString("salary"));
            job.setJobType(rs.getString("jobtype"));
            job.setAddTime(rs.getString("createtime"));
            job.setState(rs.getString("jobstate"));
            job.setAudit_opinion(rs.getString("audit_opinion"));
            job.setJobDescription(rs.getString("jobdescription"));
            job.setJobId(rs.getInt("jobid"));

            users.add(job);
        }
        return users;
    }

    @Override
    public List<Object> selectPublishedJobListById(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String pageStr = req.getParameter("page");
        System.out.println("pageStr = " + pageStr);
        String limitStr = req.getParameter("limit");
        System.out.println("limitStr = " + limitStr);
        String keyJobName = req.getParameter("keyJobName");
        System.out.println("keyName = " + keyJobName);
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyJobType = req.getParameter("keyJobType");
        String keyState = req.getParameter("keyState");
        String companyId = req.getParameter("companyId");
        System.out.println("keyJobName = " + keyJobName);
        int page1 = Integer.parseInt(pageStr);
        int limit1 = Integer.parseInt(limitStr);
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyJobName) && StringUtils.isNullOrEmpty(keyState)
                && StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(keyJobType)) {
            sql = "SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and r_job.companyid=? and (jobstate='已发布' OR jobstate='已撤销发布' ) limit ?,?";

            ps = conn.prepareStatement(sql);
            ps.setInt(2, (page1 - 1) * limit1);
            ps.setInt(3, limit1);
            ps.setString(1, companyId);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyJobName) ||
                !StringUtils.isNullOrEmpty(keyState) ||
                !StringUtils.isNullOrEmpty(keyCreateTime) ||
                !StringUtils.isNullOrEmpty(keyEndTime) ||
                !StringUtils.isNullOrEmpty(keyJobType)
        ) {
            sql = "SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户'   and jobname like ?  and jobstate like ?  and createtime >=?  and createtime  <= ?  and jobtype like ? and r_job.companyid=? and (jobstate='已发布' OR jobstate='已撤销发布' ) limit ?,?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setInt(7, (page1 - 1) * limit1);
            ps.setInt(8, limit1);
            ps.setString(6, companyId);
            ps.setString(2, "%" + keyState + "%");
            ps.setString(3, keyCreateTime);
            ps.setString(4, keyEndTime);
            ps.setString(5, "%" + keyJobType + "%");
            ps.setString(1, "%" + keyJobName + "%");
        }
        ResultSet rs = ps.executeQuery();
        List<Object> users = new ArrayList<Object>();
        while (rs.next()) {

            Job job = new Job();
            job.setCompanyId(rs.getString("companyid"));
            job.setCompanyName(rs.getString("name"));
            job.setCompanyAddress(rs.getString("jobaddress"));
            job.setJobNum(rs.getString("jobnum"));
            job.setJobName(rs.getString("jobname"));
            job.setSalary(rs.getString("salary"));
            job.setJobType(rs.getString("jobtype"));
            job.setAddTime(rs.getString("createtime"));
            job.setState(rs.getString("jobstate"));
            job.setAudit_opinion(rs.getString("audit_opinion"));
            job.setJobDescription(rs.getString("jobdescription"));
            job.setJobId(rs.getInt("jobid"));

            users.add(job);
        }
        return users;
    }

    @Override
    public List<Object> selectAllJobList(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String pageStr = req.getParameter("page");
        System.out.println("pageStr = " + pageStr);
        String limitStr = req.getParameter("limit");
        System.out.println("limitStr = " + limitStr);
        String keyJobName = req.getParameter("keyJobName");
//        System.out.println("keyName = " + keyJobName);
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyJobType = req.getParameter("keyJobType");
//        String keyState = req.getParameter("keyState");
        String keyCompanyName = req.getParameter("keyCompanyName");
        String low = req.getParameter("low");
        String high = req.getParameter("high");
        System.out.println("keyJobName = " + keyJobName);
        int page1 = Integer.parseInt(pageStr);
        int limit1 = Integer.parseInt(limitStr);
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyJobName) &&  StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(keyJobType)  && StringUtils.isNullOrEmpty(keyCompanyName)
                && StringUtils.isNullOrEmpty(low)  && StringUtils.isNullOrEmpty(high)) {
            sql = "SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and jobstate='已发布' limit ?,?";

            ps = conn.prepareStatement(sql);
            ps.setInt(1, (page1 - 1) * limit1);
            ps.setInt(2, limit1);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyJobName) ||
                !StringUtils.isNullOrEmpty(keyCreateTime) ||
                !StringUtils.isNullOrEmpty(keyEndTime) ||
                !StringUtils.isNullOrEmpty(keyJobType) ||
                !StringUtils.isNullOrEmpty(keyCompanyName) ||
                !StringUtils.isNullOrEmpty(low) ||
                !StringUtils.isNullOrEmpty(high)
        ) {
            sql = "SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and jobstate='已发布'  and jobname like ?  and createtime >=?  and createtime  <= ?  and jobtype like ? and r_company.name like ? and low > ? and high < ? limit ?,?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setInt(8, (page1 - 1) * limit1);
            ps.setInt(9, limit1);

            ps.setString(2, keyCreateTime);
            ps.setString(3, keyEndTime);
            ps.setString(4, "%" + keyJobType + "%");
            ps.setString(5, "%" + keyCompanyName + "%");
            ps.setInt(6, Integer.parseInt(low));
            ps.setInt(7, Integer.parseInt(high));
            ps.setString(1, "%" + keyJobName + "%");
        }
        ResultSet rs = ps.executeQuery();
        List<Object> users = new ArrayList<Object>();
        while (rs.next()) {

            Job job = new Job();
            job.setCompanyId(rs.getString("companyid"));
            job.setCompanyName(rs.getString("name"));
            job.setCompanyAddress(rs.getString("jobaddress"));
            job.setJobNum(rs.getString("jobnum"));
            job.setJobName(rs.getString("jobname"));
            job.setSalary(rs.getString("salary"));
            job.setJobType(rs.getString("jobtype"));
            job.setAddTime(rs.getString("createtime"));
            job.setState(rs.getString("jobstate"));
            job.setAudit_opinion(rs.getString("audit_opinion"));
            job.setJobDescription(rs.getString("jobdescription"));
            job.setJobId(rs.getInt("jobid"));
            users.add(job);
        }
        return users;
    }

    @Override
    public List<Object> selectAllLike(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String pageStr = req.getParameter("page");
        System.out.println("pageStr = " + pageStr);
        String limitStr = req.getParameter("limit");
        System.out.println("limitStr = " + limitStr);
        String keyJobName = req.getParameter("keyJobName");
//        System.out.println("keyName = " + keyJobName);
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyJobType = req.getParameter("keyJobType");
        String userId = req.getParameter("userId");
//        String keyState = req.getParameter("keyState");
        String keyCompanyName = req.getParameter("keyCompanyName");
        String low = req.getParameter("low");
        String high = req.getParameter("high");
        System.out.println("keyJobName = " + keyJobName);
        int page1 = Integer.parseInt(pageStr);
        int limit1 = Integer.parseInt(limitStr);
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyJobName) &&  StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(keyJobType)  && StringUtils.isNullOrEmpty(keyCompanyName)
                && StringUtils.isNullOrEmpty(low)  && StringUtils.isNullOrEmpty(high)) {
            sql = "SELECT * from r_job,r_company,r_favoritesjob where r_job.companyid=r_company.companyid and r_company.state='正常用户' and r_favoritesjob.userid=? and r_favoritesjob.jobid=r_job.jobid  limit ?,?";

            ps = conn.prepareStatement(sql);
            ps.setString(1,userId);
            ps.setInt(2, (page1 - 1) * limit1);
            ps.setInt(3, limit1);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyJobName) ||
                !StringUtils.isNullOrEmpty(keyCreateTime) ||
                !StringUtils.isNullOrEmpty(keyEndTime) ||
                !StringUtils.isNullOrEmpty(keyJobType) ||
                !StringUtils.isNullOrEmpty(keyCompanyName) ||
                !StringUtils.isNullOrEmpty(low) ||
                !StringUtils.isNullOrEmpty(high)
        ) {
            sql = "SELECT * from r_job,r_company,r_favoritesjob where r_job.companyid=r_company.companyid and r_company.state='正常用户' and r_favoritesjob.userid=? and r_favoritesjob.jobid=r_job.jobid   and r_job.jobname like ?  and createtime >=?  and createtime  <= ?  and jobtype like ? and r_company.name like ? and low > ? and high < ? limit ?,?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setInt(9, (page1 - 1) * limit1);
            ps.setInt(10, limit1);
            ps.setString(1,userId);
            ps.setString(3, keyCreateTime);
            ps.setString(4, keyEndTime);
            ps.setString(5, "%" + keyJobType + "%");
            ps.setString(6, "%" + keyCompanyName + "%");
            ps.setInt(7, Integer.parseInt(low));
            ps.setInt(8, Integer.parseInt(high));
            ps.setString(2, "%" + keyJobName + "%");
        }
        ResultSet rs = ps.executeQuery();
        List<Object> users = new ArrayList<Object>();
        while (rs.next()) {

            Job job = new Job();
            job.setCompanyId(rs.getString("companyid"));
            job.setCompanyName(rs.getString("name"));
            job.setCompanyAddress(rs.getString("jobaddress"));
            job.setJobNum(rs.getString("jobnum"));
            job.setJobName(rs.getString("jobname"));
            job.setSalary(rs.getString("salary"));
            job.setJobType(rs.getString("jobtype"));
            job.setAddTime(rs.getString("createtime"));
            job.setState(rs.getString("jobstate"));
            job.setAudit_opinion(rs.getString("audit_opinion"));
            job.setJobDescription(rs.getString("jobdescription"));
            job.setJobId(rs.getInt("jobid"));
            users.add(job);
        }
        return users;
    }

    @Override
    public int countJob(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String keyName = req.getParameter("keyCompanyName");
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyId = req.getParameter("keyId");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyJobType = req.getParameter("keyJobType");
        String keyState = req.getParameter("keyState");

        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyName) &&
                StringUtils.isNullOrEmpty(keyId) && StringUtils.isNullOrEmpty(keyState)
                && StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(keyJobType)) {
            sql = "SELECT count(*) as sum from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and (jobstate='未审核' or jobstate='审核通过' or jobstate='审核不通过')";
//            sql1="SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户'   and jobname like ?  and jobstate like ?  and createtime >=?  and createtime  <= ?  and jobtype like ? and r_job.companyid=? and (jobstate='已发布' OR jobstate='已撤销发布' ) limit ?,?";
            ps = conn.prepareStatement(sql);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyName) ||
                !StringUtils.isNullOrEmpty(keyId) ||
                !StringUtils.isNullOrEmpty(keyState) ||
                !StringUtils.isNullOrEmpty(keyCreateTime) ||
                !StringUtils.isNullOrEmpty(keyEndTime) ||
                !StringUtils.isNullOrEmpty(keyJobType)
        ) {
            sql = "SELECT count(*) as sum from  r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and (jobstate='未审核' or jobstate='审核通过' or jobstate='审核不通过')  and r_company.`name` like ? and r_company.companyid like ?  and jobstate like ? and createtime > ? and createtime < ? and jobtype like ?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);


            ps.setString(3, "%" + keyState + "%");
            ps.setString(2, "%" + keyId + "%");
            ps.setString(4, keyCreateTime);
            ps.setString(5, keyEndTime);
            ps.setString(6, "%" + keyJobType + "%");
            ps.setString(1, "%" + keyName + "%");
        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            return rs.getInt("sum");
        }
        return 0;
    }

    @Override
    public int countAllJob(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyJobType = req.getParameter("keyJobType");
        String keyCompanyName = req.getParameter("keyCompanyName");
        String keyJobName = req.getParameter("keyJobName");
        String low = req.getParameter("low");
        String high = req.getParameter("high");
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyJobName) &&
                StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(keyJobType)  && StringUtils.isNullOrEmpty(keyCompanyName)
                && StringUtils.isNullOrEmpty(low)  && StringUtils.isNullOrEmpty(high)) {
            sql = "SELECT count(*) as sum from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and jobstate='已发布'";
//            sql1="SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户'   and jobname like ?  and jobstate like ?  and createtime >=?  and createtime  <= ?  and jobtype like ? and r_job.companyid=? and (jobstate='已发布' OR jobstate='已撤销发布' ) limit ?,?";
            ps = conn.prepareStatement(sql);
            System.out.println("sql = " + sql);
        } else if (
                !StringUtils.isNullOrEmpty(keyCreateTime) ||
                !StringUtils.isNullOrEmpty(keyEndTime) ||
                !StringUtils.isNullOrEmpty(keyJobType)  ||
                !StringUtils.isNullOrEmpty(keyCompanyName)  ||
                !StringUtils.isNullOrEmpty(low)  ||
                !StringUtils.isNullOrEmpty(high)
        ) {
            sql = "SELECT count(*) as sum from  r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and jobstate='已发布'  and jobname like ?  and createtime >=?  and createtime  <= ?  and jobtype like ? and r_company.name like ? and low > ? and high < ?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);

            ps.setString(2, keyCreateTime);
            ps.setString(3, keyEndTime);
            ps.setString(4, "%" + keyJobType + "%");
            ps.setString(5, "%" + keyCompanyName + "%");
            ps.setInt(6, Integer.parseInt(low));
            ps.setInt(7, Integer.parseInt(high));
            ps.setString(1, "%" + keyJobName + "%");
        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            return rs.getInt("sum");
        }
        return 0;
    }

    @Override
    public int countAllLike(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyJobType = req.getParameter("keyJobType");
        String keyCompanyName = req.getParameter("keyCompanyName");
        String keyJobName = req.getParameter("keyJobName");
        String low = req.getParameter("low");
        String high = req.getParameter("high");
        String userId = req.getParameter("userId");
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyJobName) &&
                StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(keyJobType)  && StringUtils.isNullOrEmpty(keyCompanyName)
                && StringUtils.isNullOrEmpty(low)  && StringUtils.isNullOrEmpty(high)) {
            sql = "SELECT count(*) as sum from r_job,r_company,r_favoritesjob where r_job.companyid=r_company.companyid and r_company.state='正常用户' and r_favoritesjob.userid=? and r_favoritesjob.jobid=r_job.jobid";
//            sql1="SELECT * from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户'   and jobname like ?  and jobstate like ?  and createtime >=?  and createtime  <= ?  and jobtype like ? and r_job.companyid=? and (jobstate='已发布' OR jobstate='已撤销发布' ) limit ?,?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,userId);
            System.out.println("sql = " + sql);
        } else if (
                !StringUtils.isNullOrEmpty(keyCreateTime) ||
                        !StringUtils.isNullOrEmpty(keyEndTime) ||
                        !StringUtils.isNullOrEmpty(keyJobType)  ||
                        !StringUtils.isNullOrEmpty(keyCompanyName)  ||
                        !StringUtils.isNullOrEmpty(low)  ||
                        !StringUtils.isNullOrEmpty(high)
        ) {
            sql = "SELECT count(*) as sum from  r_job,r_company,r_favoritesjob where r_job.companyid=r_company.companyid and r_company.state='正常用户' and r_favoritesjob.userid=? and r_favoritesjob.jobid=r_job.jobid   and r_job.jobname like ?  and createtime >=?  and createtime  <= ?  and jobtype like ? and r_company.name like ? and low > ? and high < ? ";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setString(1,userId);
            ps.setString(3, keyCreateTime);
            ps.setString(4, keyEndTime);
            ps.setString(5, "%" + keyJobType + "%");
            ps.setString(6, "%" + keyCompanyName + "%");
            ps.setInt(7, Integer.parseInt(low));
            ps.setInt(8, Integer.parseInt(high));
            ps.setString(2, "%" + keyJobName + "%");
        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            return rs.getInt("sum");
        }
        return 0;
    }

    @Override
    public int countJobById(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String keyJobName = req.getParameter("keyJobName");
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyJobType = req.getParameter("keyJobType");
        String keyState = req.getParameter("keyState");
        String companyId = req.getParameter("companyId");

        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyJobName) && StringUtils.isNullOrEmpty(keyState)
                && StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(keyJobType)) {
            sql = "SELECT count(*) as sum from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户'   and (jobstate='未审核' or jobstate='审核通过' or jobstate='审核不通过')  and r_job.companyid=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, companyId);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyJobName) ||
                !StringUtils.isNullOrEmpty(keyState) ||
                !StringUtils.isNullOrEmpty(keyCreateTime) ||
                !StringUtils.isNullOrEmpty(keyEndTime) ||
                !StringUtils.isNullOrEmpty(keyJobType)
        ) {
            sql = "SELECT count(*) as sum from  r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户'   and (jobstate='未审核' or jobstate='审核通过' or jobstate='审核不通过')   and jobname like ?  and jobstate like ? and createtime > ? and createtime < ? and jobtype like ? and r_job.companyid=?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setString(2, "%" + keyState + "%");
            ps.setString(3, keyCreateTime);
            ps.setString(4, keyEndTime);
            ps.setString(5, "%" + keyJobType + "%");
            ps.setString(6, companyId);
            ps.setString(1, "%" + keyJobName + "%");
        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            return rs.getInt("sum");
        }
        return 0;
    }

    @Override
    public int countPublishJobById(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String keyJobName = req.getParameter("keyJobName");
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyJobType = req.getParameter("keyJobType");
        String keyState = req.getParameter("keyState");
        String companyId = req.getParameter("companyId");

        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyJobName) && StringUtils.isNullOrEmpty(keyState)
                && StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(keyJobType)) {
            sql = "SELECT count(*) as sum from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and (jobstate='审核通过' OR jobstate='已发布' OR jobstate='已撤销发布' ) and r_job.companyid=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, companyId);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyJobName) ||
                !StringUtils.isNullOrEmpty(keyState) ||
                !StringUtils.isNullOrEmpty(keyCreateTime) ||
                !StringUtils.isNullOrEmpty(keyEndTime) ||
                !StringUtils.isNullOrEmpty(keyJobType)
        ) {
            sql = "SELECT count(*) as sum from  r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户'  and jobname like ?  and jobstate like ? and createtime > ? and createtime < ? and jobtype like ? and r_job.companyid=? and (jobstate='审核通过' OR jobstate='已发布' OR jobstate='已撤销发布' )";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setString(2, "%" + keyState + "%");
            ps.setString(3, keyCreateTime);
            ps.setString(4, keyEndTime);
            ps.setString(5, "%" + keyJobType + "%");
            ps.setString(6, companyId);
            ps.setString(1, "%" + keyJobName + "%");
        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            return rs.getInt("sum");
        }
        return 0;
    }

    @Override
    public int countPublishedJobById(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String keyJobName = req.getParameter("keyJobName");
        String keyCreateTime = req.getParameter("keyCreateTime");
        String keyEndTime = req.getParameter("keyEndTime");
        String keyJobType = req.getParameter("keyJobType");
        String keyState = req.getParameter("keyState");
        String companyId = req.getParameter("companyId");

        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyJobName) && StringUtils.isNullOrEmpty(keyState)
                && StringUtils.isNullOrEmpty(keyCreateTime) && StringUtils.isNullOrEmpty(keyEndTime)
                && StringUtils.isNullOrEmpty(keyJobType)) {
            sql = "SELECT count(*) as sum from r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户' and (jobstate='已发布' OR jobstate='已撤销发布' ) and r_job.companyid=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, companyId);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyJobName) ||
                !StringUtils.isNullOrEmpty(keyState) ||
                !StringUtils.isNullOrEmpty(keyCreateTime) ||
                !StringUtils.isNullOrEmpty(keyEndTime) ||
                !StringUtils.isNullOrEmpty(keyJobType)
        ) {
            sql = "SELECT count(*) as sum from  r_job,r_company where r_job.companyid=r_company.companyid and r_company.state='正常用户'  and jobname like ?  and jobstate like ? and createtime > ? and createtime < ? and jobtype like ? and r_job.companyid=? and (jobstate='已发布' OR jobstate='已撤销发布' )";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setString(2, "%" + keyState + "%");
            ps.setString(3, keyCreateTime);
            ps.setString(4, keyEndTime);
            ps.setString(5, "%" + keyJobType + "%");
            ps.setString(6, companyId);
            ps.setString(1, "%" + keyJobName + "%");
        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            return rs.getInt("sum");
        }
        return 0;
    }

    @Override
    public int RemoveFavouriteJob(Resume resume) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String sql = "DELETE from r_favoritesjob where jobid=? and userid=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, resume.getJobId());
            ps.setString(2, resume.getUserId());
            int i = ps.executeUpdate();
            if (i == 1) {
                return 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int isFavouriteJob(FavouriteJob favouriteJob) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String sql = "";
        sql = "SELECT count(*) as sum from r_favoritesjob where userid=? and jobid=?";
        System.out.println("sql = " + sql);
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, favouriteJob.getUserId());
        ps.setInt(2, favouriteJob.getJobId());
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
        try {
            return rs.getInt("sum");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
        return 0;
    }
}
